package com.ycc.uitl;

import java.io.File;
import java.io.FileInputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.reflect.FieldUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.ycc.study.entity.SheetRow;

public class OperExcel {
    public static void main(String[] args) throws Exception{
        String filePath="D:\\work\\sjzx\\XX百旺(望)2020年X月工作总结 -财务数据模板.xlsx";
        List<Map<String, Object>>  ls=  importExcel(filePath);
        JSONArray array= JSONArray.parseArray(JSON.toJSONString(ls));
//        List<Map>  lis= getData(ls);
//        JSONArray array= JSONArray.parseArray(JSON.toJSONString(lis));
        System.out.println(array);
    }

    public static Map getDatilsListData(List  lst,XSSFSheet sheet) throws Exception{
//    	 JSONArray array= JSONArray.parseArray(JSON.toJSONString(lst));
    	 int index=lst.size();
    	 Map<String, Object> dtablemap = new HashMap<String, Object>();
    	 List<Map>  list=new ArrayList<Map>();
    		for(int i=1;i<=index;i++){
    			List dtableList=new ArrayList<>();
    			 for (int j = 1; j < sheet.getLastRowNum(); j++) {
    	             XSSFRow row= sheet.getRow(j);
    	             Map<String, Object> map = new HashMap<String, Object>();
    	             if(row!=null){
    	                 //装载obj
    	            	 map.put("cpmc", getVal(row.getCell(10+6*(i-1))));//表格2
    	           		 map.put("xssl", getVal(row.getCell(10+6*(i-1)+1)));
    	           		 map.put("xssr", getVal(row.getCell(10+6*(i-1)+2)));
    	           		 map.put("xscb", getVal(row.getCell(10+6*(i-1)+3)));
    	           		 map.put("xsmlr", getVal(row.getCell(10+6*(i-1)+4)));
    	                
    	             }
    	             dtableList.add(map);
    	         }
    			 dtablemap.put(i+"", dtableList);
    			
       	}
    	return dtablemap;
    }
    
    public  static List<Map<String, Object>> importExcel(/*MultipartFile file ,Object obj*/ String filePath) throws Exception {
        File file =new File(filePath);
        //装载流
       // POIFSFileSystem fs = new POIFSFileSystem(file.getInputStream());
        FileInputStream fs=new FileInputStream(file);
        XSSFWorkbook hw= new XSSFWorkbook(fs);
        List<Map<String, Object>> dat = new ArrayList<Map<String, Object>>();
       
        for(int j=0;j<13;j++){
        	   //获取第一个sheet页
            XSSFSheet sheet = hw.getSheetAt(j);
            Map map=new HashMap();
            //容器
            List<Map<String, Object>> ret = new ArrayList<Map<String, Object>>();
            //遍历行 从下标第一行开始（去除标题）
            List detailList=new ArrayList<>();
            int startRowIndex=0;
            int endRowIndex=0;
            int jyfyzyTableIndex=0;
            for (int i = 1; i < sheet.getLastRowNum(); i++) {
                XSSFRow row= sheet.getRow(i);
                SheetRow obj=new SheetRow();
                if(row!=null){
                    //装载obj
                	Map objDat=dataObj(obj,row);
                   int startRowIndexTem=Integer.parseInt(objDat.get("row").toString());
                   
                   if(startRowIndexTem>12 && startRowIndex==0 ){
                	   startRowIndex=startRowIndexTem;
                   }
                    if(objDat.get("A2").toString().trim().equals("自营合计")){
                    	endRowIndex=Integer.parseInt(objDat.get("row").toString())-1;
                    }
                    if(objDat.get("A2").toString().trim().equals("总计")){
                    	jyfyzyTableIndex=Integer.parseInt(objDat.get("row").toString());
                    }
                    ret.add(objDat);
                }
            }
            
           for(int i=startRowIndex;i<endRowIndex;i++){
        	   detailList.add(ret.get(i));
           }
            Map detailMap=getDatilsListData(detailList,sheet);
        	map.put(j+"_D", detailMap);
        	List reList=comData(ret, startRowIndex,endRowIndex,jyfyzyTableIndex);//经营利润 增值税业务
        	map.put(j+"_L", reList);
        	dat.add(map);
        }
        return dat;
    }

//处理有单元格合并问题
    private static List<Map<String, Object>> comData(List<Map<String, Object>> ret,int starRowNum,int endRowNum,int jyfyzyTableIndex){
    	List<Map<String, Object>>  reList=new ArrayList<>();
    	for(int i=0;i<ret.size();i++){
			Map reMap=ret.get(i);
    		if(i>=starRowNum && i<endRowNum+2){//+2自营非自营
    			reMap.put("xssl", reMap.get("A6"));
    			reMap.put("xssr", reMap.get("A7"));
    			reMap.put("xscb", reMap.get("A8"));
    			reMap.put("xsmlr", reMap.get("A9"));
    		}
    		if(i>endRowNum+2 && i<jyfyzyTableIndex){//经营费用
    			reMap.put("ljzc", reMap.get("A6"));
    		}
    		reList.add(reMap);
    	}
    	
    	return ret;
    	
    }
    
    
    private  static  Map<String, Object>  dataObj(Object obj, XSSFRow row) throws Exception {
        Class<?> rowClazz= obj.getClass();
        Field[] fields = FieldUtils.getAllFields(rowClazz);
        if (fields == null || fields.length < 1) {
            return null;
        }
        //容器
        Map<String, Object> map = new HashMap<String, Object>();
        //注意excel表格字段顺序要和obj字段顺序对齐 （如果有多余字段请另作特殊下标对应处理）
        SheetRow p=new SheetRow();
         p.setRow(row.getRowNum());
        for (int j = 0; j < row.getLastCellNum(); j++) {// fields.length 
             p.setCol(j);
        	if(j<9){
        	    map.put(fields[j].getName(), getVal(row.getCell(j)));//表格1 
        	}
        }
        map.put("row", p.getRow());
        return map;
    }

    /**
     * 处理val（暂时只处理string和number，可以自己添加自己需要的val类型）
     * @param hssfCell
     * @return
     */
    public static String getVal(XSSFCell hssfCell) {
    	if(hssfCell==null){
    		return "";
    	}else{
    		  hssfCell.setCellType(Cell.CELL_TYPE_STRING);
    		 return hssfCell.getStringCellValue();
    	} 
    }
}
